﻿//********************************************************************
// Author:      Cxq
// Create Date: 2012-08-25
// Description: 
//
// Version:     1.0.0.0
// Change History:
// Date		    Who		      Changes Made
//--------------------------------------------------------------------
// Please Insert the Modiy record here!
//--------------------------------------------------------------------
//********************************************************************



using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

using SZSE.Framework2.Common;
using SZSE.Application.LSD.Common;
using SZSE.Application.LSD.DataContract;

namespace SZSE.Application.LSD.DataAccess
{
	/// <summary>
	/// 这个类提供了对security ()表的数据库操作
	/// </summary>
	public static partial class securityManager
	{
		private static IDBAccess db=DatabaseFactory.CreateDatabase(AppConstants.BizDBInstanceName);

		static string insertSQL ="INSERT INTO [security] ([id],[stockexchangecode],[compcode],[secshortname],[seccode]) VALUES (@id,@stockexchangecode,@compcode,@secshortname,@seccode)";
	
		static string updateSQL ="Update [security] Set [id]=@id,[stockexchangecode]=@stockexchangecode,[compcode]=@compcode,[secshortname]=@secshortname,[seccode]=@seccode Where 1=1";

		static string deleteSQL ="DELETE FROM  [security]  Where 1=1";	
		
		#region codesmith生成的代码
		/// <summary>
		/// 获取securityEntity实体 
		/// </summary>
		/// <param name="id">id</param>
		/// <returns>securityEntity</returns>
		public static securityEntity GetsecurityEntity(int id)
		{
			string strSQL = "select * from security where 1=1";			
			IDbDataParameter para_id = new SqlParameter("id", SqlDbType.__UNKNOWN__integer);
			para_id.Value = id;
			IDbDataParameter[] paras=new IDbDataParameter[]{para_id};
			
			
			securityEntity entity=new securityEntity();
			using(System.Data.IDataReader reader=db.ExecuteReader(CommandType.Text, strSQL,paras))
			{
				if(reader.Read()==false) return null;
				LoadFromReader(reader, entity);
				return entity;
			}
		}		
		  
		
		
       		
		/// <summary>
		/// 更新securityEntity
		/// </summary>
		/// <param name="entity">Entity</param>	
		public static void Updatesecurity(securityEntity entity)
		{
			if(entity==null)
			{
				throw new DataAccessLayerException("参数[entity]为[null]！");
			}
			IDbDataParameter[] paras =GenerateParas(entity);		
			db.ExecuteNonQuery(CommandType.Text,updateSQL,paras);
		}
		
		
				
		
		/// <summary>
		///  删除一条记录
		/// </summary>
		/// <param name="id">id</param>		
		public static void Deletesecurity(int id)
		{
			IDbDataParameter para_id = new SqlParameter("id", SqlDbType.__UNKNOWN__integer);
			para_id.Value = id;
			IDbDataParameter[] paras=new IDbDataParameter[]{para_id};
			db.ExecuteNonQuery(CommandType.Text,deleteSQL,paras);
		}

	
		
		/// <summary>
		/// 删除多条记录
		/// </summary>
		/// <param name="ids">ids</param>		
		public static void Deletesecurity(List<int> ids)
		{
			string strSQL = "DELETE FROM  security  Where [id] in ({0}) ";
            string deleteIds = FormatHelper.ConvertListToSQLString<int>(ids);
            db.ExecuteNonQuery(string.Format(strSQL, deleteIds));
		}
		
		
	
		/// <summary>
		/// 根据分页条件获取security数据集
		/// </summary>
		/// <param name="conditions">条件</param>	
		/// <param name="sortExpression">排序表达式，为空则默认为:id</param>		
		/// <param name="pageIndex">开始页码：分页查询中的开始页码</param>
		/// <param name="pageSize">页宽：分页查询中的每页的记录数</param>
		/// <param name="recordCount">页宽：分页查询中的每页的记录数</param>		
		/// <returns>数据集(DataSet)：<br/>
        /// <table>
        /// <tr><td>字段名</td><td>字段类型</td><td>数据库类型</td><td>字段说明</td></tr>
        /// <tr><td>id</td><td>int</td><td>integer NotAllowDBNull</td><td>id</td></tr>
        /// <tr><td>stockexchangecode</td><td>string</td><td>character varying(5) AllowDBNull</td><td>stockexchangecode</td></tr>
        /// <tr><td>compcode</td><td>string</td><td>character(6) AllowDBNull</td><td>compcode</td></tr>
        /// <tr><td>secshortname</td><td>string</td><td>character varying(10) AllowDBNull</td><td>secshortname</td></tr>
        /// <tr><td>seccode</td><td>string</td><td>character(6) NotAllowDBNull</td><td>seccode</td></tr>
        /// </table></returns>
		public static List<securityEntity> GetsecuritysByCond(string conditions,string sortExpression,int pageIndex,int pageSize,out int recordCount)
		{			
			if(string.IsNullOrEmpty(sortExpression) || string.IsNullOrEmpty(sortExpression.Trim()))
			{
				sortExpression = "id";
			}

			//Prepare the query conditions
			ElbDatabase.PrepareSqlConditions(ref conditions);

			//view or table's Name
			string viewName="security";
			
			recordCount=-1;
			//Get the recordCount only when pageIndex==0 
			if(pageIndex==0)
			{
				//get the record count of this query
				string countSQL = "Select count(1) from {0} where 1=1 {1}";
				recordCount = (int) db.ExecuteScalar(string.Format(countSQL,viewName, conditions));
			}
			
			
			
			//the fields of this qurey want to get
			string fields ="*";			
			
			//get a page record 
			Int64 startNumber=pageIndex*pageSize+1;
			Int64 endNumber = startNumber + pageSize-1;
			string queryString = @"WITH [temptablefor{0}] AS
								   (  SELECT *,ROW_NUMBER() OVER (order by {1} ) as RowNumber 
									  FROM [{0}] Where 1=1 {2}
								   ) 
								   SELECT {3} FROM [temptablefor{0}] Where RowNumber between {4} and {5}";
			
			object[] paras =new object[] {viewName,sortExpression,conditions, fields, startNumber.ToString(),endNumber.ToString()};			
						
			List<securityEntity> list=new List<securityEntity>();
			
			securityEntity entity;
			using(System.Data.IDataReader reader=db.ExecuteReader(string.Format(queryString,paras)))
			{
				 while (reader.Read()==true)
				{
					entity=new securityEntity();
					LoadFromReader(reader, entity);
					list.Add(entity);
				}				
			}
			return list;
			
		}
				
		
		/// <summary>
		/// 根据条件获取security数据集
		/// </summary>
		/// <param name="conditions">条件</param>	
		/// <param name="sortExpression">排序表达式，为空则默认为:id</param>		
		/// <returns>数据集(DataSet)：<br/>
        /// <table>
        /// <tr><td>字段名</td><td>字段类型</td><td>数据库类型</td><td>字段说明</td></tr>
        /// <tr><td>id</td><td>int</td><td>integer NotAllowDBNull</td><td>id</td></tr>
        /// <tr><td>stockexchangecode</td><td>string</td><td>character varying(5) AllowDBNull</td><td>stockexchangecode</td></tr>
        /// <tr><td>compcode</td><td>string</td><td>character(6) AllowDBNull</td><td>compcode</td></tr>
        /// <tr><td>secshortname</td><td>string</td><td>character varying(10) AllowDBNull</td><td>secshortname</td></tr>
        /// <tr><td>seccode</td><td>string</td><td>character(6) NotAllowDBNull</td><td>seccode</td></tr>
        /// </table></returns>
		public static DataSet GetsecuritysByCond(string conditions,string sortExpression)
		{			
			if(sortExpression==null || sortExpression.Trim()=="")
			{
				sortExpression = "id";
			}

			//Prepare the query conditions
			ElbDatabase.PrepareSqlConditions(ref conditions);

			//view or table's Name
			string viewName="security";

			//the fields of this qurey want to get
			string fields ="*";			
			
			//get a page record 
			string queryString = "SELECT {0} FROM {1} WHERE 1=1 {2} ORDER BY {3}";
								
			object[] paras =new object[] {fields,viewName,conditions,sortExpression};			
			return db.ExecuteDataSet(string.Format(queryString,paras));			
		}
		#region private method
		/// <summary>
        /// 获取securityEntity实体数据更新参数
        /// </summary>
        /// <param name="entity">CompanyEntity</param>
        /// <returns></returns>
        private static IDbDataParameter[] GenerateParas(securityEntity entity)
        { 
			IDbDataParameter para_id = new SqlParameter("id", SqlDbType.__UNKNOWN__integer);
			para_id.Value = entity.id;
			
			IDbDataParameter para_stockexchangecode = new SqlParameter("stockexchangecode", SqlDbType.__UNKNOWN__character varying);
			para_stockexchangecode.Value = entity.stockexchangecode;
			
			IDbDataParameter para_compcode = new SqlParameter("compcode", SqlDbType.__UNKNOWN__character);
			para_compcode.Value = entity.compcode;
			
			IDbDataParameter para_secshortname = new SqlParameter("secshortname", SqlDbType.__UNKNOWN__character varying);
			para_secshortname.Value = entity.secshortname;
			
			IDbDataParameter para_seccode = new SqlParameter("seccode", SqlDbType.__UNKNOWN__character);
			para_seccode.Value = entity.seccode;
			
			return new IDbDataParameter[]{para_id,para_stockexchangecode,para_compcode,para_secshortname,para_seccode};	
        }
		
		/// <summary>
		/// 绑定数据到业务实体
		/// </summary>
		/// <param name="reader">DataReader</param>
		/// <param name="entity">业务实体</param>
		private static void LoadFromReader(IDataReader reader,securityEntity entity)
		{			
			if (reader != null && !reader.IsClosed)
			{
				//entity.Id = reader.GetInt32(0);
                if(!Convert.IsDBNull(reader["id"]))entity.id = (int)reader["id"];
                if(!Convert.IsDBNull(reader["stockexchangecode"]))entity.stockexchangecode = reader["stockexchangecode"].ToString();
                if(!Convert.IsDBNull(reader["compcode"]))entity.compcode = reader["compcode"].ToString();
                if(!Convert.IsDBNull(reader["secshortname"]))entity.secshortname = reader["secshortname"].ToString();
                if(!Convert.IsDBNull(reader["seccode"]))entity.seccode = reader["seccode"].ToString();
			}
			
		}
		#endregion	
		
		#endregion
		
		#region 手工增加的代码
       
        #endregion
		

		
	}
}

